/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cabarrus.xltosql.util;

import com.cabarrus.xltosql.XLToSQLTemplateColumn;
import com.cabarrus.xltosql.XLToSQLTemplateTable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author moscac
 */
public class DbManager {

    public DbManager() throws Exception {    // note more general exception

        Class.forName("org.hsqldb.jdbcDriver");
        conn = DriverManager.getConnection("jdbc:hsqldb:" + dbFileName, "sa", "");
        createTables();
    }

    public void shutdown() throws SQLException {

        Statement st = conn.createStatement();
        st.execute("SHUTDOWN");
        conn.close();
    }

    public List<XLToSQLTemplateTable> getTemplateTables(String dbName) {
        List<XLToSQLTemplateTable> tables = new ArrayList<XLToSQLTemplateTable>(0);
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            String sql = "select * from template_table where tt_db_name = '" +
                    dbName.trim() + "'";
            rs = stmt.executeQuery(sql);
            int i = tables.size();
            while (rs.next()) {
                tables.add(new XLToSQLTemplateTable());
                tables.get(i).setTt_id(rs.getInt("tt_id"));
                tables.get(i).setTt_db_name(rs.getString("tt_db_name"));
                tables.get(i).setTt_db_table_name(rs.getString("tt_db_table_name"));
                i++;
            }
            stmt.close();
        } catch (SQLException se) {
            JOptionPane.showMessageDialog(null, "SQLException: " + se.getMessage());
        }
        return tables;
    }

    public int updateTemplateTable(XLToSQLTemplateTable templateTable, boolean update) {
        PreparedStatement pstmt = null;
        try {
            String sql = "";
            if (update) {
                sql = "update template_table set tt_db_name = ?, tt_db_table_name = ? " +
                        "where tt_id = ?";
            } else {
                sql = "insert into template_table (tt_db_name, tt_db_table_name) values (?,?)";
            }
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, templateTable.getTt_db_name());
            pstmt.setString(2, templateTable.getTt_db_table_name());
            if (update) {
                pstmt.setInt(3, templateTable.getTt_id());
            }
            return pstmt.executeUpdate();
        } catch (SQLException se) {
        }
        return 0;
    }

    public List<XLToSQLTemplateColumn> getTemplateColumns(int tableId, String whereClause) {

        Statement stmt = null;
        ResultSet rs = null;
        List<XLToSQLTemplateColumn> templates = new ArrayList<XLToSQLTemplateColumn>(0);
        try {
            stmt = conn.createStatement();
            String sql = "select * from template_column where tc_tt_id = " + tableId;
            if (whereClause.length() > 0) {
                sql += " " + whereClause;
            }
            rs = stmt.executeQuery(sql);
            int i = templates.size();
            while (rs.next()) {
                templates.add(new XLToSQLTemplateColumn());
                templates.get(i).setTc_id(rs.getInt("tc_id"));
                templates.get(i).setTc_tt_id(rs.getInt("tc_tt_id"));
                templates.get(i).setTc_dbColumnName(rs.getString("tc_db_column_name"));
                templates.get(i).setTc_dbColumnType(rs.getInt("tc_db_column_type"));
                templates.get(i).setTc_defaultValue(rs.getString("tc_default_value"));
                templates.get(i).setTc_xlColumnName(rs.getString("tc_xl_column_name"));
                i++;
            }
            stmt.close();
        } catch (SQLException se) {
            JOptionPane.showMessageDialog(null, "SQLException: " + se.getMessage());
        }
        return templates;
    }

    public int updateTemplateColumn(XLToSQLTemplateColumn templateColumn, boolean update) {
        PreparedStatement pstmt = null;
        try {
            String sql = "";
            if (update) {
                sql = "update template_column set tc_tt_id = ?, tc_db_column_name = ?," +
                        "tc_db_column_type = ?, tc_default_value = ?, tc_xl_column_name = ?" +
                        "where tc_id = ?";
            } else {
                sql = "insert into template_column (tc_tt_id, tc_db_column_name," +
                        "tc_db_column_type, tc_default_value, tc_xl_column_name) " +
                        "values (?,?,?,?,?)";
            }
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, templateColumn.getTc_tt_id());
            pstmt.setString(2, templateColumn.getTc_dbColumnName());
            pstmt.setInt(3, templateColumn.getTc_dbColumnType());
            pstmt.setString(4, templateColumn.getTc_defaultValue());
            pstmt.setString(5, templateColumn.getTc_xlColumnName());
            if (update) {
                pstmt.setInt(5, templateColumn.getTc_id());
            }
            return pstmt.executeUpdate();
        } catch (SQLException se) {
        }
        return 0;
    }

    public int removeTemplateTable(int tt_id) {
        return removeFromTable("template_table", "tt_id", tt_id);
    }

    public int removeTemplateColumn(int tc_id) {
        return removeFromTable("template_column", "tc_id", tc_id);
    }

    private int removeFromTable(String tableName, String column, int id) {
        Statement stmt = null;
        try {
            String sql = "delete from " + tableName + " where " + column + " = " + id;
            stmt = conn.createStatement();
            return stmt.executeUpdate(sql);
        } catch (SQLException se) {
        }
        return 0;
    }

    public int executeUpdate(String expression) throws SQLException {
        Statement stmt = null;
        stmt = conn.createStatement();
        int recs = stmt.executeUpdate(expression);
        stmt.close();
        return recs;
    }

    public boolean tableExists(String tableName) {

        ResultSet tables = null;
        try {
            DatabaseMetaData meta = conn.getMetaData();
            String[] names = {"TABLE"};
            tables = meta.getTables(null, "%", "%", names);
            while (tables.next()) {
                if (tables.getString(3).equalsIgnoreCase(tableName)) {
                    return true;
                }

            }
        } catch (SQLException se) {
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return false;
    }

    private void createTables() {
        for (int i = 0; i < tableNames.length; i++) {
            if (!tableExists(tableNames[i])) {
                try {
                    executeUpdate(tableSQLs[i]);
                } catch (SQLException se) {
                }
            }
        }
    }
    private Connection conn;
    private static String dbFileName = "_xltosql";
    private static String[] tableNames = {"template_table", "template_column"};
    private static String tableSQLs[] = {
        "CREATE TABLE template_table ( tt_id integer IDENTITY," +
        "tt_db_name varchar(256), tt_db_table_name varchar(256)" +
        ")",
        "CREATE TABLE template_column ( tc_id INTEGER IDENTITY, " +
        "tc_tt_id integer," +
        "tc_db_column_name VARCHAR(256)," +
        "tc_db_column_type integer," +
        "tc_default_value varchar(256)," +
        "tc_xl_column_name varchar(256)" +
        ")"
    };
} 


